const db = require('../database/db');

const executeQuery = (sql, params = []) => {
    return new Promise((resolve, reject) => {
        db.query(sql, params, (err, data) => {
            if (err) {
                console.error('数据库查询失败：' + err);
                reject(err);
            } else {
                resolve(data);
            }
        });
    });
};

const list = () => {
    let sql = 'SELECT * FROM `sys`.`goods` LIMIT 0,1000';
    return executeQuery(sql);
};

const add = (req) => {
    let sql = 'INSERT INTO `sys`.`goods` (`name`, `price`, `stock`) VALUES (?,?,?)';
    return executeQuery(sql, [req.body.name, req.body.price, req.body.stock]);
};

const del = (req) => {
    let sql = 'DELETE FROM `sys`.`goods` WHERE `id` = ?';
    return executeQuery(sql, [req.query.id]);
};

const pud = (req) => {
    let sql = 'UPDATE `sys`.`goods` SET `name` = ?, `price` = ?, `stock` = ? WHERE `id` = ?';
    return executeQuery(sql, [req.body.name, req.body.price, req.body.stock, req.query.id]);
};

const login = ({user, password}) => {
    console.log('查询用户名：' + user + '，密码：' + password);
    let sql = 'SELECT * FROM `sys`.`user` WHERE user=? AND password=? LIMIT 1';
    return executeQuery(sql, [user, password]);
};

const add_user = (req) => {
    let sql = 'INSERT INTO `sys`.`user` (`user`, `password`) VALUES (?, ?)';
    return executeQuery(sql, [req.body.user, req.body.password]);
};

const add_list = () => {
    let sql = 'SELECT * FROM `sys`.`album` LIMIT 0,1000';
    return executeQuery(sql);
};

const add_album = (req, res) => {
    console.log('Received files:', req.files);

    const uploadedFiles = req.files.file;
    console.log('Uploaded files:', uploadedFiles);
    const filePaths = [];

    const moveFiles = new Promise((resolve, reject) => {
        const handleFile = (file) => {
            file.mv(`./public/images/${file.name}`, (err) => {
                if (err) {
                    console.error('Error moving file:', err);
                    reject(err);
                } else {
                    filePaths.push(`http://localhost:3000/public/images/${file.name}`);
                }
            });
        };

        if (Array.isArray(uploadedFiles)) {
            let count = uploadedFiles.length;
            uploadedFiles.forEach(file => {
                handleFile(file);
                count--;
                if (count === 0) resolve();
            });
        } else {
            handleFile(uploadedFiles);
            resolve();
        }
    });

    moveFiles.then(() => {
        console.log('File paths:', filePaths);

        if (filePaths.length === 0) {
            throw new Error('No file paths to save');
        }

        let sql = 'INSERT INTO `sys`.`album` (`title`, `cover`) VALUES (?, ?)';
        return executeQuery(sql, [req.body.name, filePaths[0]]);
    }).then(() => {
        console.log('File path saved to database:', filePaths);
        res.send({ code: 200, data: filePaths });
    }).catch((error) => {
        console.error('Error saving file path to database:', error);
        res.status(500).send('Error saving file path to database');
    });
};

const del_album = (req) => {
    console.log(req.body.id);
    let sql = 'DELETE FROM `sys`.`album` WHERE `id` = ?';
    return executeQuery(sql, [req.body.id]);
};

const pud_album = (req) => {
    console.log('Received album id for update:', req.body.id);

    const uploadedFiles = req.files.file;
    const filePaths = [];

    const handleFile = (file) => {
        file.mv(`./public/images/${file.name}`, (err) => {
            if (err) {
                console.error('Error moving file:', err);
            } else {
                filePaths.push(`http://localhost:3000/public/images/${file.name}`);
            }
        });
    };

    if (Array.isArray(uploadedFiles)) {
        uploadedFiles.forEach(handleFile);
    } else {
        handleFile(uploadedFiles);
    }

    console.log('File paths:', filePaths);

    let sql = 'UPDATE `sys`.`album` SET `title` = ?, `cover` = ? WHERE `id` = ?';
    return executeQuery(sql, [req.body.name, filePaths[0] || req.body.cover, req.body.id]);
};

const sel_album = (req) => {
    console.log(req.body.id);
    let sql = 'SELECT * FROM `sys`.`img` WHERE `pid` = ?';
    return executeQuery(sql, [req.body.id]);
};

const add_img = (req) => {
    console.log('Received files:', req.files);

    const uploadedFiles = req.files.file;
    console.log('Uploaded files:', uploadedFiles);
    const filePaths = [];

    const moveFiles = new Promise((resolve, reject) => {
        const handleFile = (file) => {
            file.mv(`./public/images/${file.name}`, (err) => {
                if (err) {
                    console.error('Error moving file:', err);
                    reject(err);
                } else {
                    filePaths.push(`http://localhost:3000/public/images/${file.name}`);
                }
            });
        };

        if (Array.isArray(uploadedFiles)) {
            let count = uploadedFiles.length;
            uploadedFiles.forEach(file => {
                handleFile(file);
                count--;
                if (count === 0) resolve();
            });
        } else {
            handleFile(uploadedFiles);
            resolve();
        }
    });

    return moveFiles.then(() => {
        if (filePaths.length === 0) {
            throw new Error('No file paths to save');
        }

        let sql = 'INSERT INTO `sys`.`img` (`pid`, `img`) VALUES (?, ?)';
        return executeQuery(sql, [req.body.id, filePaths[0]]);
    }).then(() => {
        console.log('File path saved to database:', filePaths);
        return filePaths;
    }).catch((error) => {
        console.error('Error saving file path to database:', error);
        throw error;
    });
};

const del_img = (req) => {
    console.log(req.body.id);
    let sql = 'DELETE FROM `sys`.`img` WHERE `id` = ?';
    return executeQuery(sql, [req.body.id]);
};

const get_app = () => {
    let sql = 'SELECT * FROM `sys`.`app` LIMIT 0,1000';
    return executeQuery(sql);
};

const add_app = (req) => {
    let sql = 'INSERT INTO `sys`.`app` (`user`, `content`) VALUES (?, ?)';
    return executeQuery(sql, [req.body.user, req.body.content]);
};

const del_app = (req) => {
    let sql = 'DELETE FROM `sys`.`app` WHERE `id` = ?';
    return executeQuery(sql, [req.body.id]);
};

const get_reply = () => {
    let sql = 'SELECT * FROM `sys`.`app_reply` LIMIT 0,1000';
    return executeQuery(sql);
};

const add_reply = (req) => {
    let sql = 'INSERT INTO `sys`.`app_reply` (`user`, `content`, `pid`) VALUES (?, ?, ?)';
    return executeQuery(sql, [req.body.user, req.body.content, req.body.pid]);
};

module.exports = {
    list,
    add,
    del,
    pud,
    login,
    add_user,
    add_list,
    add_album,
    del_album,
    pud_album,
    sel_album,
    add_img,
    del_img,
    get_app,
    add_app,
    del_app,
    get_reply,
    add_reply,
};
